import psycopg2
import pandas as pd
from datetime import datetime

# 数据库连接配置
DB_CONFIG = {
    'dbname': 'heytea_db',
    'user': 'heytea',  # 确保使用具有足够权限的用户
    'password': '666666',
    'host': '127.0.0.1',
    'port': '5432'
}

def insert_weather_data(data):
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        cursor = conn.cursor()

        insert_query = """
        INSERT INTO daily_weather (
            date, highest_temperature, lowest_temperature, temperature_difference,
            average_temperature, average_humidity, weather, wind_direction, 
            precipitation_24h, body_temperature, sunrise_sunset, moonrise_moonset,
            air_quality_index, daylight_hours, sunshine_duration, short_wave_radiation_total
        ) VALUES (
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
        )
        """

        for row in data:
            print(f"插入数据:{row}")
            cursor.execute(insert_query, (
                row['日期'], row['最高温度'], row['最低温度'], row['温差'],
                row['平均温度'], row['平均湿度'], row['天气'], row['风向/风速'],
                row['24h降水量'], row['体感温度'], row['日出/日落'], row['月升/月落'],
                row['空气质量AQI'], row['白昼时长(秒)'], row['日照时长(秒)'],
                row['短波辐射总量(MJ/m²)']
            ))

        conn.commit()
        print("数据插入成功")

    except Exception as e:
        print(f"插入数据时发生错误: {e}")

    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

def extract_temperature_value(temp_str):
    if temp_str and '℃' in temp_str:
        return float(temp_str.replace('℃', ''))
    return None


def extract_precipitation_value(temp_str):
    if temp_str and 'mm' in temp_str:
        return float(temp_str.replace('mm', ''))
    return None

def read_excel_and_insert_to_db(file_path):
    # 读取Excel文件
    df = pd.read_excel(file_path)

    # 将DataFrame转换为字典列表
    data = df.to_dict('records')
      # 处理温度字段，提取数值
    for row in data:
        row['最高温度'] = extract_temperature_value(row['最高温度'])
        row['最低温度'] = extract_temperature_value(row['最低温度'])
        row['温差'] = extract_temperature_value(row['温差'])
        row['平均温度'] = extract_temperature_value(row['平均温度'])
        row['体感温度'] = extract_temperature_value(row['体感温度'])
        row['24h降水量'] = extract_precipitation_value(row['24h降水量'])

    print(data)
    # 插入数据到数据库
    insert_weather_data(data)

if __name__ == "__main__":
    excel_file_path = "3月天气数据.xlsx"  # 替换为你的Excel文件路径
    read_excel_and_insert_to_db(excel_file_path)